import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
from sklearn.decomposition import PCA
from sklearn.preprocessing import StandardScaler
import os

plt.rcParams['font.sans-serif'] = ['SimHei']
plt.rcParams['axes.unicode_minus'] = False


def pca_analysis():
    file_path = r"D:\PythonProject3\data.xlsx"
    try:
        df = pd.read_excel(file_path)
        print("数据读取成功！数据形状:", df.shape)
    except Exception as e:
        print(f"读取文件失败: {e}")
        return

    features = df[['语文', '数学', '英语']]
    student_ids = df['学号']

    scaler = StandardScaler()
    features_scaled = scaler.fit_transform(features)

    scaled_df = pd.DataFrame(features_scaled, columns=['语文_标准化', '数学_标准化', '英语_标准化'])
    scaled_df['学号'] = student_ids.values

    pca = PCA()
    principal_components = pca.fit_transform(features_scaled)

    pca_scores = pd.DataFrame(principal_components,
                              columns=[f'PC{i + 1}' for i in range(principal_components.shape[1])])
    pca_scores['学号'] = student_ids.values

    variance_table = pd.DataFrame({
        '主成分': [f'PC{i + 1}' for i in range(len(pca.explained_variance_ratio_))],
        '特征值': pca.explained_variance_,
        '方差贡献率': pca.explained_variance_ratio_,
        '累计方差贡献率': np.cumsum(pca.explained_variance_ratio_)
    })

    eigenvectors = pd.DataFrame(pca.components_.T, columns=[f'PC{i + 1}' for i in range(pca.components_.shape[0])],
                                index=['语文', '数学', '英语'])

    loadings = pd.DataFrame(pca.components_.T * np.sqrt(pca.explained_variance_),
                            columns=[f'PC{i + 1}' for i in range(pca.components_.shape[0])],
                            index=['语文', '数学', '英语'])

    # 保存表格到Excel
    output_dir = os.path.dirname(file_path)
    output_file = os.path.join(output_dir, 'pca_analysis_results.xlsx')

    with pd.ExcelWriter(output_file, engine='openpyxl') as writer:
        scaled_df.to_excel(writer, sheet_name='标准化数据', index=False)
        variance_table.to_excel(writer, sheet_name='方差分析表', index=False)
        eigenvectors.to_excel(writer, sheet_name='特征向量表')
        pca_scores.to_excel(writer, sheet_name='主成分得分表', index=False)
        loadings.to_excel(writer, sheet_name='载荷矩阵')

    print(f"所有表格已保存至: {output_file}")

    # 生成图表
    plt.figure(figsize=(15, 6))

    # 主成分分析散点图
    plt.subplot(1, 2, 1)
    scatter = plt.scatter(principal_components[:, 0], principal_components[:, 1], c=student_ids, cmap='viridis', s=100,
                          alpha=0.7)
    for i, txt in enumerate(student_ids):
        plt.annotate(f'{txt}', (principal_components[i, 0], principal_components[i, 1]), xytext=(8, 8),
                     textcoords='offset points', fontsize=8, alpha=0.8)
    plt.colorbar(scatter, label='学号')
    plt.xlabel(f'PC1 ({variance_table.iloc[0, 2]:.2%})')
    plt.ylabel(f'PC2 ({variance_table.iloc[1, 2]:.2%})')
    plt.title('主成分分析散点图')
    plt.grid(True, alpha=0.3)

    # 方差贡献分析柏拉图
    plt.subplot(1, 2, 2)
    components = range(1, len(pca.explained_variance_ratio_) + 1)
    plt.bar(components, pca.explained_variance_ratio_, alpha=0.6, color='skyblue', label='单个贡献率')
    plt.plot(components, np.cumsum(pca.explained_variance_ratio_), 'ro-', label='累计贡献率')
    for i, (v, c) in enumerate(zip(pca.explained_variance_ratio_, np.cumsum(pca.explained_variance_ratio_))):
        plt.text(i + 1, v, f'{v:.2%}', ha='center', va='bottom', fontsize=9)
        plt.text(i + 1, c, f'{c:.2%}', ha='center', va='bottom', fontsize=9)
    plt.xlabel('主成分')
    plt.ylabel('方差贡献率')
    plt.title('方差贡献分析柏拉图')
    plt.legend()
    plt.grid(True, alpha=0.3)

    plt.tight_layout()
    plt.savefig(os.path.join(output_dir, 'pca_result.png'), dpi=300, bbox_inches='tight')
    plt.show()

    print(f"图表已保存至: {os.path.join(output_dir, 'pca_result.png')}")

    # 打印关键结果
    print("\n关键结果:")
    print(f"累计方差贡献率: {variance_table.iloc[1, 3]:.2%} (前两个主成分)")
    print(f"PC1载荷: 语文={loadings.iloc[0, 0]:.3f}, 数学={loadings.iloc[1, 0]:.3f}, 英语={loadings.iloc[2, 0]:.3f}")


if __name__ == "__main__":
    pca_analysis()